<?php
  /**
   * @param $response - result from a mysql_query call.
   * @output A table representing $response
   * @return - N/A
   * @include tools/ColumnNames.php
   * 
   * @example of use:
   *
   * EchoTable(mysql_query("SELECT * FROM tablename LIMIT 0,5"))
   *
   * Displays the 5 first entries from tablename
   */
function EchoTable($response) {
    // Get Column Names
    $i = 0;
    while($i < mysql_num_fields($response)) {
        $meta = mysql_fetch_field($response,$i);
        $column[$i] = $meta->name;
        $i++;
    }
    
    // Show first line of the table
    echo "\n\n<table frame=box>\n<tr bgcolor=#000077>";
    $n = count($column);
    for($i=0; $i<$n; $i++) {
        echo "\n<td><font color=#ffffff>".$column[$i]."</font></td>";
    }
    echo "\n</tr>";

    // Show data
    $row = mysql_fetch_row($response);
    while($row != false) {
        $n = count($row);
        echo "\n<tr>";
        for($i=0; $i<$n; $i++) {
            if($column[$i]=="Image") {
                echo "\n<td><img height=50px src=\"$row[$i]\" /></td>";
            }
            else{
                echo "\n<td>".$row[$i]."</td>";
            }
        }
        echo "\n</tr>";
        $row = mysql_fetch_row($response);
    }
    echo "\n</table>\n";
}

  /**
   * @param $sql_query - A MySQL SELECT query.
   * @output A table representing the result of the query.
   * @return - N/A
   * 
   * @example of use:
   *
   * EchoSelectQuery("SELECT * FROM tablename LIMIT 0,5");
   *
   * Displays the 5 first entries from tablename
   */
function EchoSelectQuery($sql_query) {
    echo "<p class=sql_cmd>MySQL > ".$sql_query."</p>";

    include "tools/connect.php";
    $response = mysql_query($sql_query);
    if($response) {
        EchoTable($response); echo "<br/>";
    }
    else {
        echo "<p class=sql_error>MySQL says > ".mysql_error()."</p>";
    }
    mysql_close();
}


function EchoSelectQuery_items($sql, $tablename) {
    echo "<p class=sql_cmd>MySQL > ".$sql."</p>";
    
    include "tools/connect.php";
    $response = mysql_query($sql);
    if(!$response)
        { echo "<p class=sql_error>MySQL says > ".mysql_error()."</p>"; }
    
    // Get Column Names / ID field name = $column[0]
    $i = 0;
    while($i < mysql_num_fields($response)) {
        $meta = mysql_fetch_field($response,$i);
        $column[$i] = $meta->name;
        $i++;
    }

    $n = count($column);
    $row = mysql_fetch_row($response);
    while($row != false) {
        // Show the main table
        echo "\n\n<br/><table frame=box>\n<tr bgcolor=#000077>";
        
        for($i=0; $i<$n; $i++) {
            echo "\n<td><font color=#ffffff>".$column[$i]."</font></td>"; }
        echo "\n</tr>\n<tr>";
        for($i=0; $i<$n; $i++) {
            echo "\n<td>".$row[$i]."</td>"; }
        echo "\n</tr>";

        $sql = "SELECT itemID,qty,cost FROM $tablename WHERE $column[0]='$row[0]'";
        $items = mysql_query($sql);
        EchoTable($items);
        $row = mysql_fetch_row($response);
        echo "<br/>";
    }
    
    mysql_close();
}

  /**
   * @param $sql_query - A MySQL INSERT query.
   * @param $table - The name of the table.
   * @output An HTML table of the inserted record.
   * @return - the ID of the inserted record.
   * 
   * @example of use:
   *
   * $id = EchoInsertQuery("INSERT INTO tablename(f2) VALUES(5)","tablename");
   * DoSomthing($id)
   *
   * Inserts a record of value 5 for field f2 and displays it.
   */
function EchoInsertQuery($sql_query, $table) {
    echo "<p class=sql_cmd>MySQL > ".$sql_query."</p>";

    include "tools/connect.php";

    $response = mysql_query($sql_query);
    if($response == 0) {
        echo '<p class=sql_error>'.mysql_error().'</p>'; 
        mysql_close();
        return;
    }

    $id = mysql_insert_id();
    if($id == 0) {
        echo "<p class=sql_error>Cannot display result. ID 0 received.</p>";
        mysql_close(); return 0;
    }
        
    $sql_query = "SELECT *".
        " FROM $table".
        " WHERE ".GetKeyFieldName($table)."='$id.'";
    $response = mysql_query($sql_query);
    EchoTable($response); echo "<br/>";

    mysql_close();
    return $id;
}

  /**
   * @param $itemID - Type of item to add
   * @param $qty - Quantity of item <itemID>
   * @param $facilityID - Facility to withdraw stock from
   * @output Error message if there isn't enough stock.
   * @return - FALSE if there isn't enough stock.
   *         - total sell-cost otherwise.
   * 
   * @example of use:
   *
   * $total_sell_cost = WithdrawStockFromFacility(1, 4, 3);
   *
   * Inserts a record of value 5 for field f2 and displays it.
   */
function WithdrawStockFromFacility($itemID, $qty, $facilityID) {
    include "tools/connect.php";

    // CHECK STOCK :
    $sql = "SELECT Amount FROM itemStock ".
        "WHERE facilityID='$facilityID' AND itemID='$itemID'";
    $response= mysql_query($sql);
    if(!$response) {echo mysql_error(); mysql_close(); return;}

    $row = mysql_fetch_row($response);
    if(!$row OR $row[0]<$qty) {
        // Error. Either item doesn't exist or there isn't enough stock.
        $response= mysql_query("SELECT * FROM items WHERE itemID='$itemID'");
        if(!mysql_fetch_row($response)) {
            echo "<p class=sql_error>Item $itemID doesn't exist.</p>";
        }
        else {
            echo "<p class=sql_error>Not enough stock of item ".$itemID
                ." in facility ".$facilityID.".</p>";
        }
        mysql_close();
        return FALSE;
    }

    // UPDATE STOCK :
    $sql = "UPDATE itemStock SET Amount=".($row[0] - $qty).
        " WHERE facilityID='$facilityID' AND itemID='$itemID'";
    $response= mysql_query($sql);
    if(!$response) {echo mysql_error(); mysql_close(); return;}    

    // GET COST :
    $sql = "SELECT SellCost FROM items WHERE itemID='$itemID'";
    $response= mysql_query($sql);
    if(!$response) {echo mysql_error(); mysql_close(); return;}

    $row = mysql_fetch_row($response);
    if(!$row) {echo '<p class=sql_error>Item not found.</p>'; mysql_close(); return;}

    mysql_close();
    return $qty * $row[0];
}

  /**
   * @param $itemID - Type of item to add
   * @param $qty - Quantity of item <itemID>
   * @param $facilityID - Facility to add stock to.
   * @output N/A
   * @return - result from the MySQL query
   * @note If the stock entry exists, a MySQL UPDATE is performed.
   *       Otherwise the record is created with a MySQL INSERT.
   * 
   * @example of use:
   *
   * $mysql_response = AddStockToFacility(1, 4, 3);
   *
   * Adds 4 of item 1 to the stock of facility 3.
   */
function AddStockToFacility($itemID, $qty, $facilityID) {
    include "tools/connect.php";
    
    // Check if that stock entry exists.
    $sql = "SELECT stockID,Amount FROM itemStock ".
        "WHERE itemID='$itemID' AND facilityID='$facilityID'";
    $response= mysql_query($sql);
    if(!$response) {echo "<br/>select stock:".mysql_error(); mysql_close(); return;}
    $row = mysql_fetch_row($response);

    // Doesn't exist, create it.
    if(!$row) {
        $sql = "INSERT INTO itemStock(itemID, facilityID, Amount) ".
            "VALUES('$itemID', '$facilityID', $qty)";
    }

    // Exists, update it.
    else {
        $qty = $qty + $row[1];
        $sql = "UPDATE itemStock SET Amount=".$qty." WHERE stockID=".$row[0];
    }

    // Query :
    $response = mysql_query($sql);
    if(!$response) {echo "<br/>Update/insert".mysql_error(); mysql_close(); return;}
    mysql_close();

    return $response;
}

?>